
import tkinter as tk
import tkinter.messagebox
import pymysql
from pymysql.converters import escape_string


class ConnectMysql:
    def __init__(self):
        self.host = "116.63.136.129"
        self.username = "root"
        self.pwd = "Root@1234"
        self.chart = "users"
        self.dbname = "GameUser"
        self.port = 3306
        try:
            self.db = pymysql.connect(
                host=self.host,
                user=self.username,
                passwd=self.pwd,
                database=self.dbname,
                port=self.port,
                charset='utf8')
            self.cursor = self.db.cursor()
        except Exception as e:
            print("Error:{0}".format(e))

    # 规格设计：
    # 用户名：由数字组成，限定为202176564（9位）
    # 密码：由数字和小写字母组成，限定为6位

    def UpdateSql(self, name, score):
        sql = '''
        UPDATE users SET score = {0} WHERE user_id = {1}
            '''.format(score, name)
        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print("wrong {0}".format(e))
        finally:
            self.db.commit()

    def DeleteAql(self, name, score):
        sql = '''
        DELETE FROM 表名称 WHERE name={0} and score={1}

        '''.format(name, score)

        try:
            self.cursor.execute(sql)
        except Exception as e:
            self.db.rollback()
            print("wrong {0}".format(e))
        finally:
            self.db.commit()

    def ReadSql(self):
        sql = "SELECT * FROM users"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()  # 获取全部结果集。  fetchone 查询第一条数据
        if not results:  # 判断是否为空。
            print("数据为空！")
            return False
        else:
            scorelist = []
            for row in results:
                id = row[0]
                pwd = row[1]
                score = row[2]
                scorelist.append((id, score))
                # Sex = row[3]
                # Salary = row[4]
                # 打印结果
                # print("id:{0}姓名:{1}年龄:{2}性别:{3}工资:{4}".format(Id,Name,Age,Sex,Salary))

            scorelist = sorted(scorelist, key=lambda x: (
                int(x[1]), int(x[0])), reverse=True)

            return scorelist

    def ReadOneSql(self, name):
        if name == "":
            return False
        sql = "SELECT score FROM users where user_id={0}".format(name)
        sql = escape_string(sql)
        self.cursor.execute(sql)
        results = self.cursor.fetchall()  # 获取全部结果集。  fetchone 查询第一条数据
        if not results:  # 判断是否为空。
            print("数据为空！")
            return False
        else:

            return results[0][0]

    def dbclose(self):
        self.db.close()


class ScoreListWindow:
    def __init__(self):
        self.scoreListWindow = tk.Tk()
        self.scoreListWindow.title("贪吃蛇SCORE排行榜")
        self.scoreListWindow.geometry('500x300')
        tk.Label(
            self.scoreListWindow,
            text="贪吃蛇Score排行榜",
            bg='#ff8533',
            fg='white',
            font=(
                'Arial',
                12),
            width=30,
            height=2).pack()
        tk.Button(self.scoreListWindow, text='Quit', bg='#d98cb3', fg='white', font=(
            'Arial', 12), width=10, height=1, command=self.scoreListWindow.quit).pack()
        self.frame = tk.Frame(self.scoreListWindow)
        self.frame.pack()

        self.frame_l = tk.Frame(self.frame)
        self.frame_r = tk.Frame(self.frame)
        self.frame_m = tk.Frame(self.frame)
        self.frame_l.pack(side='left')
        self.frame_m.pack(side='left')
        self.frame_r.pack(side='right')
        tk.Label(
            self.frame_l,
            text="用户名称",
            fg='black',
            font=(
                'Arial',
                12),
            height=2).pack()
        tk.Label(
            self.frame_m,
            text="Score",
            fg='black',
            font=(
                'Arial',
                12),
            height=2).pack()
        tk.Label(
            self.frame_r,
            text="排行",
            fg='black',
            font=(
                'Arial',
                12),
            height=2).pack()

        self.mydb = ConnectMysql()
        scorelist = self.mydb.ReadSql()
        score = [i[1] for i in scorelist]
        names = [i[0] for i in scorelist]
        score = score[:min(10, len(score))]
        names = names[:min(10, len(names))]
        # 用户id
        var = tk.StringVar()
        var.set(names)
        tk.Listbox(self.frame_l, listvariable=var, bg='#9999ff').pack()
        # score
        var = tk.StringVar()
        var.set(score)
        tk.Listbox(self.frame_m, listvariable=var, bg='#9999ff').pack()
        # 排名
        var = tk.StringVar()
        var.set([i for i in range(min(len(scorelist), 10))])
        tk.Listbox(self.frame_r, listvariable=var, bg='#9999ff').pack()

    def showList(self):
        self.scoreListWindow.mainloop()

    def updateList(self, name, now_score):
        now_max_score = self.mydb.ReadOneSql(name)
        if int(now_score) > int(now_max_score):
            self.mydb.UpdateSql(name, str(now_score))
            return True
        else:
            return False
